package cz.acies.data;

import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.ServletContext;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.lang3.StringUtils;
import org.h2.tools.Csv;

import cz.acies.data.DBConnector;
import cz.acies.data.dao.DefaultMapListHandler;
import cz.acies.data.dao.DefaultRowProcessor;
import cz.acies.uniql.dialect.Dialect;
import cz.acies.uniql.metadata.DBMetaData;
import cz.acies.uniql.metadata.TableMetaData;
import cz.acies.uniql.sql.CreateTable;
import cz.acies.uniql.sql.InsertQuery;
import cz.acies.uniql.sql.SelectQuery;
import cz.acies.uniql.sql.element.UniQLCondition;
import cz.acies.uniql.sql.element.UniQLValue;
import cz.acies.uniql.sql.element.UniQLWhere;

public abstract class DBUpadaterAsbtract
{
	protected Connection conn;
	protected DBMetaData dbmd;
	protected QueryRunner qr;
	protected ServletContext sCtx;
	
	protected Set<String> updates = new HashSet<String>();

	public DBUpadaterAsbtract() {}

	public DBUpadaterAsbtract(Connection conn, DBMetaData dbmd) {
		this.conn = conn;
		this.dbmd = dbmd;
	}
	
	public void execute() {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			initialize();
			
			//System.out.println("DBUpadaterAsbtract.execute( START )");
			Dialect dialect = dbmd.getDialect();
			qr = new QueryRunner(DBConnector.instance().getDataSource());
			stmt = dialect.createStatement(conn);
			String table = "";
			
			updateStructureCommons();		
			updateDataCommons();
			updateStructure();
			updateData();

		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			try { if (rs != null) rs.close(); } catch (Exception e) {}
			try { if (stmt != null) stmt.close(); } catch (Exception e) {}
		}
	}
	
	// -----  -----------------------------------------------------------------
	
	protected void initialize() throws Exception {
		qr = new QueryRunner(DBConnector.instance().getDataSource());
	}
	
	public abstract void updateStructure() throws Exception;
	
	public abstract void updateData() throws Exception;
	
	public void updateStructureCommons() throws Exception {
		//Dialect dialect = dbmd.getDialect();
		//qr = new QueryRunner(DBConnector.instance().getDataSource());
		String table = "";

		// ----- DMI ----------------------------------------------------------
		
		table = "dmi_updates";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "descript", Types.VARCHAR, 4096, 0, false, "", false, "", conn);
		}
		
		table = "gui_modul";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "modulId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "label", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "image", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "pageUrl", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "treeViewer", Types.VARCHAR, 128, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "tableViewer", Types.VARCHAR, 128, 0, false, "", true, "", conn);  
		}

		// ----- GUI -------------------------------------------------------------
		
		table = "gui_modul_menu";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "parentId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "label", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "image", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "priority", Types.INTEGER, 11, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "modulId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
		}

		table = "gui_modul_user_define";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 64, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "userId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "modulId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "objectId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "parentId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "paramKey", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "paramValue", Types.VARCHAR, 255, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "content", Types.VARCHAR, 4096, 0, false, "", true, "", conn);
		}

		// ----- Other --------------------------------------------------------
		
		table = "dictionary";
		if (createModelTable(""+table)) {				
			dbmd.updateColumn(table, "lngId", Types.VARCHAR, 3, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "valuText", Types.VARCHAR, 255, 0, false, "", true, "", conn);
		}

		table = "numeral_type";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "groupId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "header", Types.VARCHAR, 20, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "dictId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
		}
		
		// prefix = "AC.N"
		table = "country";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "number", Types.INTEGER, 11, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "code", Types.VARCHAR, 3, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "code2", Types.VARCHAR, 2, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "name", Types.VARCHAR, 64, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "dictCode", Types.VARCHAR, 20, 0, false, "", true, "", conn);
		}

		// ----- Users --------------------------------------------------------
		
		table = "user_person";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "firstName", Types.VARCHAR, 50, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "lastName", Types.VARCHAR, 50, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "mailId", Types.VARCHAR, 20, 0, false, "", true, "", conn);
		}
		
		table = "user_login";
		if (createModelTable(""+table)) {
			dbmd.updateColumn(table, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(table, "userName", Types.VARCHAR, 50, 0, false, "", true, "", conn);
			dbmd.updateColumn(table, "userPassword", Types.VARCHAR, 50, 0, false, "", true, "", conn);
			//dbmd.updateStructureColumn(tableName, columnName, sqlType, size, decimal, nullable, value, pk, unique, autoincrement, isIndex, actionType, conn)
			dbmd.updateColumn(table, "personId", Types.VARCHAR, 20, 0, false, "", false, true, false, false, "", conn);
		}

	}
	
	public void updateDataCommons() throws Exception {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			if (!updates.contains("000.000.001")) {
				String srcPath = "/cz/acies/data/country.csv";
				InputStream fis = getClass().getResourceAsStream(srcPath);
				Csv csv = new Csv();
				csv.setFieldSeparatorRead(';');
				csv.setWriteColumnHeader(false);
				
				rs = csv.read(new InputStreamReader(fis), "NUMBER;CODE;CODE2;NAME".split(";"));
				int cnt = 0;
				while( rs.next()) {
					String code = "" + rs.getString("CODE");
					String id = "AC.N." + StringUtils.leftPad(""+code, 15, "0");
					String number = "" + rs.getString("NUMBER");
					String code2 = "" + rs.getString("CODE2");
					String name = "" + rs.getString("NAME");
					
					if (cnt > 0) {
						UniQLWhere where = new UniQLWhere().add(new UniQLCondition("country.code", "=", "'"+code+"'"));
						if (!recordExists("country", where)) {
							qr.update(new InsertQuery(dbmd).into("country")
								.addParams(
									new String[] { "id", "number", "code", "code2", "name" }, 
									new Object[] { "'"+id+"'", ""+number+"", "'"+code+"'", "'"+code2+"'", "'"+name+"'" }
								).toSQLString()
							);
						}
					}
					cnt++;
				}
				closeBlock("000.000.001", "");
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			try { if (rs != null) rs.close(); } catch (Exception e) {}
			try { if (stmt != null) stmt.close(); } catch (Exception e) {}
		}
	}

	// -----  -----------------------------------------------------------------

	public void createModul(String id, String modulId, String label, String image, String pageUrl) {
		try {
			if (!recordExists("gui_modul", id)) {
				InsertQuery isql = new InsertQuery(dbmd).into("gui_modul").addParams(
					new String[] { "id", "modulId", "label", "image", "pageUrl" }, 
					new Object[] {"'"+id+"'", "'"+modulId+"'", "'"+label+"'", "'"+image+"'", "'"+pageUrl+"'"}
				);
				qr.update(isql.toSQLString());
			}		
		} catch (Exception exc) {
			exc.printStackTrace();
		}
	}

	public void createModulMenu(String id, String parentId, String label, String image, int priority, String modulId) {
		try {
			if (!recordExists("gui_modul_menu", id)) {
				InsertQuery isql = new InsertQuery(dbmd).into("gui_modul_menu").addParams(
					new String[] { "id", "parentId", "label", "image", "priority", "modulId"}, 
					new Object[] {"'"+id+"'", "'"+parentId+"'", "'"+label+"'", "'"+image+"'", new UniQLValue(""+priority), "'"+modulId+"'"}
				);
				qr.update(isql.toSQLString());
			}		
		} catch (Exception exc) {
			exc.printStackTrace();
		}
	}
	
	/**
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	protected boolean recordExists(String tableName, UniQLWhere where) {
		try {  
			SelectQuery sql = new SelectQuery(dbmd).addFrom(tableName).addWhere(where);
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			return !list.isEmpty();  
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return false;
	}

	protected boolean recordExists(String tableName, String id) {
		try {  
			SelectQuery sql = new SelectQuery(dbmd).addFrom(tableName).add(new UniQLCondition("id", "=", "'"+id+"'"));
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			return !list.isEmpty();  
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return false;
	}

	protected boolean createModelTable(String tableName) {
		Statement stmt = null;
		try {
			if (dbmd.containsKey(""+tableName)) return true;
			TableMetaData tbmd = this.dbmd.getTable(""+tableName);
			if (tbmd == null) {
				Dialect dialect = this.dbmd.getDialect(); 
				stmt = dialect.createStatement(conn);
				
				tbmd = new TableMetaData(dialect, dbmd);
				tbmd.setName(tableName);
				tbmd.addField(tableName, "id", Types.VARCHAR, 20, 0, false, "", true, false, false);
				
				CreateTable table = new CreateTable(this.dbmd, tbmd);
				Map<String, String> ext = new LinkedHashMap<String, String>();
				ext.put("ENGINE", "MyISAM");
				table.setParams(ext);
				stmt.execute(table.toSQLString());
				dbmd.addTable(tbmd);
			}
		} catch (Exception exc) {
			exc.printStackTrace();
			return false;
		} finally {
			if (stmt != null) try { stmt.close(); } catch (Exception e) {}
		}
		return true;
	}

	protected boolean createModulAuditTable(String tableName) {
		Statement stmt = null;
		try {
			if (!dbmd.containsKey(""+tableName)) {
				createModelTable(""+tableName);
			}
			dbmd.updateColumn(tableName, "id", Types.VARCHAR, 20, 0, false, "", true, false, false, false, "", conn);
			dbmd.updateColumn(tableName, "writerId", Types.VARCHAR, 20, 0, false, "WX.L.0000000000ADMIN", true, "", conn);
			dbmd.updateColumn(tableName, "writeDate", Types.TIMESTAMP, 19, 0, false, "1900-01-01 00:00:00", true, "", conn);
			dbmd.updateColumn(tableName, "editorId", Types.VARCHAR, 20, 0, false, "WX.L.0000000000ADMIN", true, "", conn);
			dbmd.updateColumn(tableName, "editDate", Types.TIMESTAMP, 19, 0, false, "1900-01-01 00:00:00", true, "", conn);

		} catch (Exception exc) {
			exc.printStackTrace();
			return false;
		} finally {
			if (stmt != null) try { stmt.close(); } catch (Exception e) {}
		}
		return true;
	}

	// -----  -----------------------------------------------------------------
	
	public Connection conn() {
		return getConnection();
	}

	public Connection getConnection() {
		return conn;
	}

	public void setConnection(Connection conn) {
		this.conn = conn;
	}

	public DBMetaData dbmd() {
		return getDBMetaData();
	}

	public DBMetaData getDBMetaData() {
		return dbmd;
	}

	public void setDBMetaData(DBMetaData dbmd) {
		this.dbmd = dbmd;
	}

	public ServletContext sCtx() {
		return sCtx;
	}

	public void setServletContext(ServletContext sCtx) {
		this.sCtx = sCtx;
	}

	// -----  ----------------------------------------------------------------
	
	protected Set<String> updates() {
		Statement stmt = null;
		try {
			if (updates == null || updates.isEmpty()) {
				Dialect dialect = dbmd.getDialect();
				QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
				stmt = dialect.createStatement(conn);
				
				String table = "dmi_updates";
				SelectQuery sql = new SelectQuery(dbmd).addField("dmi_updates.id").addFrom(table);
				List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
				for (Map<String, Object> map : list) {
					updates.add("" + map.get("id"));
				}
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			try { if (stmt != null) stmt.close(); } catch (Exception e) {}
		}
		return updates;
	}
	
	/**
	 * 
	 * @param id
	 * @param descript
	 */
	public void closeBlock(String id, String descript) {
		try {
			UniQLWhere where = new UniQLWhere().add(new UniQLCondition("id", "=", "'"+id+"'"));
			if (!recordExists("dmi_updates", where)) {
				qr.update(new InsertQuery(dbmd).into("dmi_updates").addParams(new String[] { "id", "descript" }, new Object[] { "'"+id+"'", "'"+descript+"'" }).toSQLString());
			}
			updates.add(id);
		} catch (Exception exc) {
			exc.printStackTrace();
		}
	}

}
